package common.main.cnki;

import java.sql.*;
import java.util.List;

public class CnkiSaveDataToSql {

    private static Connection conn = null;
    private static Statement stmt = null;

    private static void connectDatabase() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String databaseHost = CnkiConfig.mysqlHost;//
        String databaseName = CnkiConfig.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = CnkiConfig.mysqlUserName;// MySQL默认的root账户名
        String password = CnkiConfig.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"
                +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8";
        if(conn == null || conn.isClosed()){
            conn = DriverManager.getConnection(url);
        }
        if(stmt == null || stmt.isClosed()){
            stmt = conn.createStatement();
        }
    }

    public static String getAllCnkiMd5(){
        try {
            connectDatabase();
            String sql = "select md5 from CnkiPaper";

            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("md5") + ",");
            }
            return sb.toString();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void saveCnkiPaperToSql(List<CnkiData> paperList) {
        try{
            connectDatabase();
            String sql_model = "INSERT INTO CnkiPaper (title,author,author_affiliation,brief,keywords,paper_download_num,years_cite_num," +
                "citeAuthors,author_pub_download_num,journal_affectoi,journal_download_cite_num,journal,md5,paper_pub_time)" +
                "values ('<title>','<author>','<author_affiliation>','<brief>','<keywords>','<paper_download_num>','<years_cite_num>'," +
                "'<citeAuthors>','<author_pub_download_num>','<journal_affectoi>','<journal_download_cite_num>','<journal>','<md5>','<paper_pub_time>')";
            for(int i = 0;i < paperList.size();i++){
                CnkiData cnkiData = paperList.get(i);

                String sql = sql_model.replace("<title>",cnkiData.getTitle())
                   .replace("<author>",cnkiData.getAuthor())
                   .replace("<author_affiliation>",cnkiData.getAuthor_affiliation())
                   .replace("<brief>",cnkiData.getBrief())
                   .replace("<keywords>",cnkiData.getKeywords())
                   .replace("<paper_download_num>",cnkiData.getPaper_download_num() + "")
                   .replace("<years_cite_num>",cnkiData.getYears_cite_num())
                   .replace("<citeAuthors>",cnkiData.getCiteAuthors())
                   .replace("<author_pub_download_num>",cnkiData.getAuthor_pub_download_num() + "")
                   .replace("<journal_affectoi>",cnkiData.getJournal_affectoi())
                   .replace("<journal_download_cite_num>",cnkiData.getJournal_download_cite_num() + "")
                   .replace("<journal>",cnkiData.getJournal() + "")
                   .replace("<md5>",cnkiData.getMd5())
                   .replace("<paper_pub_time>",cnkiData.getPaper_pub_time());

                try{
                    stmt.executeUpdate(sql);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            conn.close();
            stmt.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }


}
